﻿Public Class TMyIndexes
    Private _List As New List(Of TMyIndex)
    Private _Table As IHasColumns
    Private ReadOnly Property Query() As TMyQuery
        Get
            Return _Table.Query
        End Get
    End Property
    Public Sub New(ByVal ATable As IHasColumns)
        _Table = ATable
    End Sub
    Public ReadOnly Property Count() As Integer
        Get
            Return _List.Count
        End Get
    End Property
    Default Public ReadOnly Property Items(ByVal Index As Integer) As TMyIndex
        Get
            Return _List(Index)
        End Get
    End Property
    Public ReadOnly Property PrimaryKey() As TMyIndex
        Get
            For Each idx As TMyIndex In _List
                If idx.IndexName.Equals("PRIMARY") Then
                    Return idx
                End If
            Next
            Return Nothing
        End Get
    End Property

    Public ReadOnly Property UniqueKeys() As List(Of TMyIndex)
        Get
            Dim lst As New List(Of TMyIndex)
            For Each idx As TMyIndex In _List
                If idx.NonUnique.Equals(0) Then
                    lst.Add(idx)
                End If
            Next
            Return lst
        End Get
    End Property

    Public ReadOnly Property NonUniqueBTreeIndex() As List(Of TMyIndex)
        Get
            Dim lst As New List(Of TMyIndex)
            For Each idx As TMyIndex In _List
                If Not idx.IndexName.Equals("PRIMARY") AndAlso idx.NonUnique.Equals(1) AndAlso idx.IndexType.Equals("BTREE") Then
                    lst.Add(idx)
                End If
            Next
            Return lst
        End Get
    End Property

    Public ReadOnly Property IndexList() As Dictionary(Of String, List(Of TMyIndex))
        Get
            Dim dic As New Dictionary(Of String, List(Of TMyIndex))
            If Me.PrimaryKey IsNot Nothing Then
                Dim pklst As New List(Of TMyIndex)
                pklst.Add(Me.PrimaryKey)
                dic.Add("PRIMARY", pklst)
            End If
            If Me.UniqueKeys IsNot Nothing Then
                dic.Add("UNIQUE", Me.UniqueKeys)
            End If
            If Me.NonUniqueBTreeIndex IsNot Nothing Then
                dic.Add("BTREE", Me.NonUniqueBTreeIndex)
            End If
            Dim typeList As List(Of String) = Me.EtcIndexTypeList
            For Each s As String In typeList
                Dim lst As New List(Of TMyIndex)
                For Each idx As TMyIndex In _List
                    If idx.IndexType.Equals(s) Then
                        lst.Add(idx)
                    End If
                Next
                dic.Add(s, lst)
            Next
            Return dic
        End Get
    End Property
    Public ReadOnly Property EtcIndexTypeList() As List(Of String)
        Get
            Dim lst As New List(Of String)
            For Each idx As TMyIndex In _List
                If Not lst.Contains(idx.IndexType) Then
                    If Not (idx.IsPrimaryKey OrElse idx.IsUnique OrElse idx.IsBTree) Then
                        lst.Add(idx.IndexType)
                    End If
                End If
            Next
            Return lst
        End Get
    End Property
    Public Sub Refresh()
        Dim indexSchema As String = String.Empty
        Dim indexName As String = String.Empty
        For i As Integer = 0 To _List.Count - 1
            _List(i).Clear()
        Next
        _List.Clear()
        Query.Clear()
        Query.SQL.AppendLine("select")
        Query.SQL.AppendLine(" TABLE_CATALOG")
        Query.SQL.AppendLine(",TABLE_SCHEMA")
        Query.SQL.AppendLine(",TABLE_NAME")
        Query.SQL.AppendLine(",NON_UNIQUE")
        Query.SQL.AppendLine(",INDEX_SCHEMA")
        Query.SQL.AppendLine(",INDEX_NAME")
        Query.SQL.AppendLine(",SEQ_IN_INDEX")
        Query.SQL.AppendLine(",COLUMN_NAME")
        Query.SQL.AppendLine(",COLLATION")
        Query.SQL.AppendLine(",CARDINALITY")
        Query.SQL.AppendLine(",SUB_PART")
        Query.SQL.AppendLine(",PACKED")
        Query.SQL.AppendLine(",NULLABLE")
        Query.SQL.AppendLine(",INDEX_TYPE")
        Query.SQL.AppendLine(",COMMENT")
        Query.SQL.AppendLine(",INDEX_COMMENT")
        Query.SQL.AppendLine("from")
        Query.SQL.AppendLine(" information_schema.statistics")
        Query.SQL.AppendLine("where")
        Query.SQL.AppendLine("table_schema=?schema_name")
        Query.SQL.AppendLine("and table_name=?table_name")
        Query.SQL.AppendLine("order by")
        Query.SQL.AppendLine(" case when index_name='PRIMARY' then 1 else 2 end asc")
        Query.SQL.AppendLine(",non_unique asc")
        Query.SQL.AppendLine(",index_type")
        Query.SQL.AppendLine(",index_name asc")
        Query.SQL.AppendLine(",seq_in_index asc")

        Query.DeclareAndSet("schema_name", _Table.TableSchema)
        Query.DeclareAndSet("table_name", _Table.TableName)
        Query.Execute()
        Dim idx As TMyIndex = Nothing
        While Not Query.Eof
            If Not (indexSchema.Equals(Query.FieldAsString("INDEX_SCHEMA")) AndAlso indexName.Equals(Query.FieldAsString("INDEX_NAME"))) Then
                indexSchema = Query.FieldAsString("INDEX_SCHEMA")
                indexName = Query.FieldAsString("INDEX_NAME")
                idx = New TMyIndex
                idx._TableCatalog = Query.FieldAsString("TABLE_CATALOG")
                idx._TableSchema = Query.FieldAsString("TABLE_SCHEMA")
                idx._TableName = Query.FieldAsString("TABLE_NAME")
                idx._NonUnique = Query.FieldAsInteger("NON_UNIQUE") = 1
                idx._IndexSchema = Query.FieldAsString("INDEX_SCHEMA")
                idx._IndexName = Query.FieldAsString("INDEX_NAME")
                idx._Cardinality = Query.FieldAsInteger("CARDINALITY")
                idx._SubPart = Query.FieldAsInteger("SUB_PART")
                idx._Packed = Query.FieldAsString("PACKED")
                idx._Nullable = Query.FieldAsString("NULLABLE")
                idx._IndexType = Query.FieldAsString("INDEX_TYPE")
                idx._Comment = Query.FieldAsString("COMMENT")
                idx._IndexComment = Query.FieldAsString("INDEX_COMMENT")
                _List.Add(idx)
            End If

            Dim col As New TMyIndexColumn
            col._TableCatalog = Query.FieldAsString("TABLE_CATALOG")
            col._TableSchema = Query.FieldAsString("TABLE_SCHEMA")
            col._TableName = Query.FieldAsString("TABLE_NAME")
            col._NonUnique = Query.FieldAsInteger("NON_UNIQUE") = 1
            col._IndexSchema = Query.FieldAsString("INDEX_SCHEMA")
            col._IndexName = Query.FieldAsString("INDEX_NAME")
            col._SeqInIndex = Query.FieldAsInteger("SEQ_IN_INDEX")
            col._ColumnName = Query.FieldAsString("COLUMN_NAME")
            col._Collation = Query.FieldAsString("COLLATION")
            col._Cardinality = Query.FieldAsInteger("CARDINALITY")
            col._SubPart = Query.FieldAsInteger("SUB_PART")
            col._Packed = Query.FieldAsString("PACKED")
            col._Nullable = Query.FieldAsString("NULLABLE")
            col._IndexType = Query.FieldAsString("INDEX_TYPE")
            col._Comment = Query.FieldAsString("COMMENT")
            col._IndexComment = Query.FieldAsString("INDEX_COMMENT")
            idx.Add(col)
            Query.MoveNext()
        End While
        Query.Close()
    End Sub
End Class
